// models/SysPost.js
const mongoose = require("mongoose");
const { Schema } = mongoose;

/**
 * 系统岗位表 - 对应 MySQL sys_post 表
 */
const sysPostSchema = new Schema(
  {
    // 岗位ID - 对应MySQL的post_id
    postId: {
      type: Number,
      required: true,
      unique: true,
      index: true,
      comment: "岗位ID",
    },

    // 岗位编码 - 对应MySQL的post_code
    postCode: {
      type: String,
      required: [true, "岗位编码不能为空"],
      trim: true,
      uppercase: true,
      minlength: [1, "岗位编码至少1个字符"],
      maxlength: [64, "岗位编码不能超过64个字符"],
      match: [/^[A-Z0-9_]+$/, "岗位编码只能包含大写字母、数字和下划线"],
      index: true,
      comment: "岗位编码",
    },

    // 岗位名称 - 对应MySQL的post_name
    postName: {
      type: String,
      required: [true, "岗位名称不能为空"],
      trim: true,
      minlength: [1, "岗位名称至少1个字符"],
      maxlength: [50, "岗位名称不能超过50个字符"],
      index: true,
      comment: "岗位名称",
    },

    // 显示顺序 - 对应MySQL的post_sort
    postSort: {
      type: Number,
      required: [true, "显示顺序不能为空"],
      min: [0, "显示顺序不能小于0"],
      max: [9999, "显示顺序不能超过9999"],
      comment: "显示顺序",
    },

    // 状态 - 对应MySQL的status
    status: {
      type: String,
      required: [true, "状态不能为空"],
      enum: {
        values: ["0", "1"],
        message: "状态必须是 0(正常) 或 1(停用)",
      },
      index: true,
      comment: "状态（0正常 1停用）",
    },

    // 创建者 - 对应MySQL的create_by
    createBy: {
      type: String,
      default: "",
      comment: "创建者",
    },

    // 创建时间 - 对应MySQL的create_time
    createTime: {
      type: Date,
      default: Date.now,
      comment: "创建时间",
    },

    // 更新者 - 对应MySQL的update_by
    updateBy: {
      type: String,
      default: "",
      comment: "更新者",
    },

    // 更新时间 - 对应MySQL的update_time
    updateTime: {
      type: Date,
      default: Date.now,
      comment: "更新时间",
    },

    // 备注 - 对应MySQL的remark
    remark: {
      type: String,
      default: null,
      maxlength: [500, "备注不能超过500个字符"],
      comment: "备注",
    },
  },
  {
    // Schema 选项
    timestamps: false,
    collection: "sys_posts",
    toJSON: { virtuals: true },
    toObject: { virtuals: true },
  }
);

// 创建复合索引
sysPostSchema.index({ status: 1, postSort: 1 });
sysPostSchema.index({ postCode: 1 }, { unique: true });
sysPostSchema.index({ postName: 1 });
sysPostSchema.index({ createTime: -1 });

// 虚拟字段
sysPostSchema.virtual("isActive").get(function () {
  return this.status === "0";
});

sysPostSchema.virtual("statusText").get(function () {
  const statusMap = { 0: "正常", 1: "停用" };
  return statusMap[this.status] || "未知";
});

// 实例方法
sysPostSchema.methods = {
  // 启用/禁用岗位
  toggleStatus: function () {
    this.status = this.status === "0" ? "1" : "0";
    this.updateTime = new Date();
    return this.save();
  },

  // 更新岗位信息
  updatePost: function (updateData) {
    Object.keys(updateData).forEach((key) => {
      if (key !== "postId" && this.schema.paths[key]) {
        this[key] = updateData[key];
      }
    });
    this.updateTime = new Date();
    return this.save();
  },

  // 获取岗位基本信息（用于展示）
  toBasicJSON: function () {
    return {
      postId: this.postId,
      postCode: this.postCode,
      postName: this.postName,
      postSort: this.postSort,
      status: this.status,
      statusText: this.statusText,
      remark: this.remark,
    };
  },

  // 验证岗位编码格式
  validatePostCode: function () {
    const postCodeRegex = /^[A-Z0-9_]+$/;
    return postCodeRegex.test(this.postCode);
  },
};

// 静态方法
sysPostSchema.statics = {
  // 根据岗位ID查找岗位
  findByPostId: function (postId) {
    return this.findOne({ postId: postId });
  },

  // 根据岗位编码查找岗位
  findByPostCode: function (postCode) {
    return this.findOne({
      postCode: postCode.toUpperCase(),
    });
  },

  // 根据岗位名称查找岗位
  findByPostName: function (postName) {
    return this.findOne({
      postName: new RegExp(`^${postName}$`, "i"),
    });
  },

  // 获取所有有效岗位（按显示顺序排序）
  findActivePosts: function () {
    return this.find({
      status: "0",
    }).sort({ postSort: 1 });
  },

  // 获取所有岗位（包含停用的）
  findAllPosts: function () {
    return this.find({}).sort({ postSort: 1, postId: 1 });
  },

  // 分页查询岗位
  findPostsPaginated: function (options = {}) {
    const {
      page = 1,
      limit = 10,
      postCode = "",
      postName = "",
      status = "",
    } = options;

    const skip = (page - 1) * limit;
    const query = {};

    // 构建查询条件
    if (postCode) {
      query.postCode = { $regex: postCode, $options: "i" };
    }

    if (postName) {
      query.postName = { $regex: postName, $options: "i" };
    }

    if (status) {
      query.status = status;
    }

    return Promise.all([
      this.find(query).skip(skip).limit(limit).sort({ postSort: 1, postId: 1 }),
      this.countDocuments(query),
    ]).then(([posts, total]) => ({
      posts,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        pages: Math.ceil(total / limit),
      },
    }));
  },

  // 岗位统计
  getPostStats: function () {
    return this.aggregate([
      {
        $group: {
          _id: "$status",
          count: { $sum: 1 },
          avgPostSort: { $avg: "$postSort" },
          minPostSort: { $min: "$postSort" },
          maxPostSort: { $max: "$postSort" },
        },
      },
      {
        $project: {
          status: "$_id",
          count: 1,
          avgPostSort: { $round: ["$avgPostSort", 2] },
          minPostSort: 1,
          maxPostSort: 1,
          _id: 0,
        },
      },
      {
        $sort: { status: 1 },
      },
    ]);
  },

  // 检查岗位编码是否已存在
  isPostCodeExists: function (postCode, excludePostId = null) {
    const query = {
      postCode: postCode.toUpperCase(),
    };

    if (excludePostId) {
      query.postId = { $ne: excludePostId };
    }

    return this.findOne(query);
  },

  // 检查岗位名称是否已存在
  isPostNameExists: function (postName, excludePostId = null) {
    const query = {
      postName: new RegExp(`^${postName}$`, "i"),
    };

    if (excludePostId) {
      query.postId = { $ne: excludePostId };
    }

    return this.findOne(query);
  },

  // 批量更新岗位状态
  batchUpdateStatus: function (postIds, status, updateBy = "") {
    return this.updateMany(
      { postId: { $in: postIds } },
      {
        status: status,
        updateBy: updateBy,
        updateTime: new Date(),
      }
    );
  },

  // 获取最大显示顺序
  getMaxPostSort: function () {
    return this.findOne()
      .sort({ postSort: -1 })
      .select("postSort")
      .then((post) => (post ? post.postSort : 0));
  },

  // 获取下一个可用的岗位ID
  getNextPostId: async function () {
    const lastPost = await this.findOne().sort({ postId: -1 }).select("postId");

    return lastPost ? lastPost.postId + 1 : 1;
  },

  // 根据岗位ID列表获取岗位信息
  findPostsByIds: function (postIds) {
    return this.find({
      postId: { $in: postIds },
    }).sort({ postSort: 1 });
  },

  // 搜索岗位（编码或名称）
  searchPosts: function (keyword, options = {}) {
    const {
      page = 1,
      limit = 10,
      status = "",
      fields = ["postCode", "postName"],
    } = options;

    const skip = (page - 1) * limit;
    const query = {
      $or: [],
    };

    // 构建搜索条件
    if (fields.includes("postCode")) {
      query.$or.push({ postCode: { $regex: keyword, $options: "i" } });
    }

    if (fields.includes("postName")) {
      query.$or.push({ postName: { $regex: keyword, $options: "i" } });
    }

    if (fields.includes("remark") && keyword) {
      query.$or.push({ remark: { $regex: keyword, $options: "i" } });
    }

    // 如果没有有效的搜索条件，移除 $or
    if (query.$or.length === 0) {
      delete query.$or;
    }

    // 状态筛选
    if (status) {
      query.status = status;
    }

    return Promise.all([
      this.find(query).skip(skip).limit(limit).sort({ postSort: 1, postId: 1 }),
      this.countDocuments(query),
    ]).then(([posts, total]) => ({
      posts,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        pages: Math.ceil(total / limit),
      },
    }));
  },
};

// 查询助手
sysPostSchema.query = {
  // 按状态筛选
  byStatus: function (status) {
    return this.where("status").equals(status);
  },

  // 按显示顺序排序
  sortByOrder: function (direction = "asc") {
    const sortOrder = direction === "desc" ? -1 : 1;
    return this.sort({ postSort: sortOrder });
  },

  // 搜索岗位编码或名称
  searchByKeyword: function (keyword) {
    return this.where({
      $or: [
        { postCode: { $regex: keyword, $options: "i" } },
        { postName: { $regex: keyword, $options: "i" } },
      ],
    });
  },

  // 按创建时间范围筛选
  byCreateTimeRange: function (startDate, endDate) {
    return this.where("createTime").gte(startDate).lte(endDate);
  },

  // 按更新时间范围筛选
  byUpdateTimeRange: function (startDate, endDate) {
    return this.where("updateTime").gte(startDate).lte(endDate);
  },
};

// 中间件
sysPostSchema.pre("save", function (next) {
  // 更新更新时间
  this.updateTime = new Date();

  // 岗位编码转大写
  if (this.isModified("postCode")) {
    this.postCode = this.postCode.toUpperCase();
  }

  // 验证岗位编码格式
  if (this.isModified("postCode") && !this.validatePostCode()) {
    return next(
      new Error("岗位编码格式不正确，只能包含大写字母、数字和下划线")
    );
  }

  next();
});

// 创建模型
const SysPost = mongoose.model("SysPost", sysPostSchema);

module.exports = SysPost;
